SQL*Trace

If you know the program / module / job running slowly, then you can trace the session with SQL*Trace.

Add a line near the beginning of your program that executes the following SQL:

Alter session set sql_trace = true

For example

In Pro*C

EXEC SQL Alter session set sql_trace = true;

In PL*SQL

execute immediate 'Alter session set sql_trace = true';

If you are using a tool that does not allow you to submit SQL directly to the RDBMS (eg. A front-end reporting tool) then you will need the DBA to start an Oracle Trace for you.

Now when you run the program, Oracle will generate a trace file that you can analyze using TK*Prof.

Some sites may have jobs that make repeated connections to the database, perhaps by submitting each step separately to SQL*Plus. If this is the case, the alter session above will not help, as it only affects a single connection to the database. If this is the case, you will need to either add the alter session command to each call, or – and this is a last resort - revert to a full Database Trace.